<?php class MDrj extends CI_Model {
	
	 function __construct()
	    {
		parent::__construct();
	    }

	function getAllRegion() {
		$data = array();
		$Q = $this->db->get('region');
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
			$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}
	
	function getAllCommune($id_region) {
		$data = array();
		$sql = "SELECT * FROM commune where id_region = '$id_region'";		
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
			$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}

	function getAllCommuneJson($id_region) {
		$json = array();
		$data = array();
		$sql = "SELECT * FROM commune where id_region = '$id_region'";		
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$json[$row['id_commune']][] = $row['nomcommune'];
			}
			$json = json_encode($json);
			
		}
		$Q->free_result();
		header('Content-Type: application/json');
		echo $json;
	}
	
	function getAllFokontany($id_region, $id_commune) {
		$data = array();
		$sql = "SELECT * FROM fokontany where id_region = '$id_region' and id_commune='$id_commune'";		
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
			$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}

	function getAllFokontanyJson($id_region, $id_commune) {
		$json = array();
		$data = array();
		//$sql = "SELECT * FROM fokontany where id_region = '$id_region' and id_commune='$id_commune'";
		$sql = "SELECT * FROM fokontany where id_region = '$id_commune' and id_commune='$id_region'";		
		//echo $sql;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$json[$row['id_fokontany']][] = $row['nom_fokontany'];
			}
			$json = json_encode($json);
			
		}
		$Q->free_result();
		header('Content-Type: application/json');
		echo $json;
	}

	function insertRegion($data){
		return $this->db->insert('region', $data);
	}

	function insertMaisonJeune($data){
		return $this->db->insert('etablissement', $data);
	}
	
	function insertKiosque($data){
		return $this->db->insert('etablissement', $data);
	}
	
	function insertEspaceJeune($data){
		return $this->db->insert('etablissement', $data);
	}

	function insertCoinJeune($data){
		return $this->db->insert('etablissement', $data);
	}
		
	function insertMembre($data){
		return $this->db->insert('membre', $data);
	}
	
	function insertJPE($data){
		return $this->db->insert('membre', $data);
	}
	
	function insertSensibilisation($data){
		return $this->db->insert('sensibilisation', $data);
	}
	
	function insertAssociation($data){
		return $this->db->insert('association', $data);
	}
	
	function insertFormationLocale($data){
		return $this->db->insert('formation', $data);
	}
	
	function insertSanteSexReprod($data){
		return $this->db->insert('santeSexReprod', $data);
	}
	
	function insertAlcoolismeDrogue($data){
		return $this->db->insert('alcoolismeDrogue', $data);
	}
	
	function insertAbandonEtude($data){
		return $this->db->insert('abandonEtude', $data);
	}
	
	function insertEmploi($data){
		return $this->db->insert('emploi', $data);
	}	
	
	function insertCommune($data){
		return $this->db->insert('commune', $data);
	}

	function insertFokontany($data){
		return $this->db->insert('fokontany', $data);
	}
	
	function insertEtablissement($data){
		return $this->db->insert('etablissement', $data);
	}
	
	function insertActiviteStructure($data){
		return $this->db->insert('activite', $data);
	}
	
	function insertActiviteTandeM($data){
		return $this->db->insert('activite', $data);
	}
	
	function insertCCJ($data){
		return $this->db->insert('ccj', $data);
	}

	function insertMembreCCJ($data){
		return $this->db->insert('membreccj', $data);
	}
	
	function insertVolet($data){
		return $this->db->insert('volet', $data);
	}
	function insertAbandonScolaire($data){
		return $this->db->insert('abandonScolaire', $data);
	}
	function insertPersonnel($data){
		return $this->db->insert('situationPersonnelle', $data);
	}
	
	function insertactivitecanet($data){
		return $this->db->insert('canet', $data);
	}

	function verifEtablissement ($nometablissement, $institution, $adresse){
		$nometablissement = mysql_real_escape_string($nometablissement);
		$institution = mysql_real_escape_string($institution);
		$adresse = mysql_real_escape_string($adresse);
		$sql = "SELECT id_etablissement FROM etablissement where nom_etablissement = '$nometablissement' and institution='$institution' and adresse_etablissement='$adresse'";	
		$i= 0;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$i=$row['id_etablissement'];
			}
		}
		$Q->free_result();
		return $i;
	}

	function verifRegion ($nom_region){
		$sql = "SELECT id_region FROM region where nomregion = '$nom_region'";	
		$i= 0;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$i=$row['id_region'];
			}
		}
		$Q->free_result();
		return $i;
	}

	function verifCommune ($id_region , $nomcommune){
		$sql = "SELECT id_commune FROM commune where id_region = '$id_region' and nomcommune='$nomcommune'";	
		$i= 0;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$i=$row['id_commune'];
			}
		}
		$Q->free_result();
		return $i;
	}

	function verifFokontany ($id_region , $id_commune, $nomfokontany){
		$sql = "SELECT id_fokontany FROM fokontany where id_region = '$id_region' and id_commune='$id_commune' and nom_fokontany='$nomfokontany'";	
		$i= 0;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$i=$row['id_fokontany'];
			}
		}
		$Q->free_result();
		return $i;
	}

	
	
	public function maxidcommune($id_region) {
        	$sql = "SELECT max(id_commune) as max FROM commune where id_region = '$id_region'";	
		$i= 0;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$i=$row['max'];
			}
		}
		$Q->free_result();
		return $i;
    	}

	public function maxidfokontany($id_region, $id_commune) {
        	$sql = "SELECT max(id_fokontany) as max FROM fokontany where id_region = '$id_region' and id_commune='$id_commune'";	
		$i= 0;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$i=$row['max'];
			}
		}
		$Q->free_result();
		return $i;
    	}

	public function maxidregion() {
        	$sql = "SELECT max(id_region) as max FROM region";	
		$i= 0;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$i=$row['max'];
			}
		}
		$Q->free_result();
		return $i;
    	}

	
	public function getInfoJeune($matr, $ident){
		$data = array();
		$sql = "SELECT * FROM membre
		INNER JOIN etablissement ON membre.id_etablissement = etablissement.id_etablissement
		INNER JOIN region ON membre.id_region = region.id_region
		INNER JOIN commune ON membre.id_commune = commune.id_commune
		INNER JOIN fokontany ON membre.id_fokontany = fokontany.id_fokontany 
		where membre.matricule='$matr' and etablissement.numid ='$ident' and membre.type_membre=1";	
		//echo $sql;
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
			$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}
	

	public function gettype() {
        	$sql = "SELECT distinct(type) as typedis FROM materiel";	
		$i= 0;	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$data[]=$row;
			}
		return $data;
		}
    }

	function getAllJeune() {
		$data = array();
		$sql = "SELECT * FROM membre
		INNER JOIN etablissement ON membre.id_etablissement = etablissement.id_etablissement
		INNER JOIN region ON membre.id_region = region.id_region
		INNER JOIN commune ON membre.id_commune = commune.id_commune
		INNER JOIN fokontany ON membre.id_fokontany = fokontany.id_fokontany 
		where membre.type_membre=1";		
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
			$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}

	function getAllJPE() {
		$data = array();
		$sql = "SELECT * FROM membre
		INNER JOIN etablissement ON membre.id_etablissement = etablissement.id_etablissement
		INNER JOIN region ON membre.id_region = region.id_region
		INNER JOIN commune ON membre.id_commune = commune.id_commune
		INNER JOIN fokontany ON membre.id_fokontany = fokontany.id_fokontany 
		where membre.type_membre=2";		
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
			$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}

	function getAllAssociation() {
		$data = array();
		$sql = "SELECT * FROM association
		INNER JOIN ccj ON association.id_ccj = ccj.id_ccj";
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}

	function getAllAssociationCCJ($id_ccj) {
		$data = array();
		$sql = "SELECT * FROM association
		INNER JOIN ccj ON association.id_ccj = ccj.id_ccj where association.id_ccj=$id_ccj";
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}
	
	function getAllEtabl($type) {
		$data = array();
		$sql = "";
		if($type == 0){
			$sql = "SELECT * FROM etablissement
			INNER JOIN region ON etablissement.id_region = region.id_region
			INNER JOIN commune ON etablissement.id_commune = commune.id_commune
			INNER JOIN fokontany ON etablissement.id_fokontany = fokontany.id_fokontany 
		";
		}
		else {
			$sql = "SELECT * FROM etablissement
		INNER JOIN region ON etablissement.id_region = region.id_region
		INNER JOIN commune ON etablissement.id_commune = commune.id_commune
		INNER JOIN fokontany ON etablissement.id_fokontany = fokontany.id_fokontany WHERE etablissement.type_etab = '$type' 
		";
		}
		
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}

	function getAllCCJ() {
		$data = array();
		$sql = "SELECT * FROM ccj 
		INNER JOIN region ON ccj.id_region = region.id_region";	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}
	
	function getAllFormation($id) {
		$data = array();
		$sql = "";
		if ($id!=-1){
			$sql = "SELECT * FROM formation
			INNER JOIN etablissement ON formation.id_etablissement = etablissement.id_etablissement
			WHERE formation.id_etablissement = $id
			";
		}
		else {
			$sql = "SELECT * FROM formation
			INNER JOIN etablissement ON formation.id_etablissement = etablissement.id_etablissement
			";
		}
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}

	function getAllRecherche ($idregion, $idcommune, $idfokontany, $classe) {
		$data = array();
		//echo "<br />".$idregion.":".$idcommune.":".$idfokontany.":".$iddetenteur.":". $idauditeur.":". $type;
		$sql = "SELECT *
			FROM membre
			INNER JOIN region ON membre.id_region = region.id_region
			INNER JOIN commune ON membre.id_commune = commune.id_commune
			INNER JOIN fokontany ON membre.id_fokontany = fokontany.id_fokontany
			WHERE membre.type_membre = $classe";
		if($idregion!=0 && $idcommune!=-1 && $idfokontany!=-1){
			$sql = $sql."
			AND membre.id_region = $idregion
			AND membre.id_commune = $idcommune
			AND membre.id_fokontany = $idfokontany
			";
		}
		
		else if($idregion!=0 && $idcommune!=-1 && $idfokontany==-1){
			$sql = $sql."
			AND membre.id_region = $idregion
			AND membre.id_commune = $idcommune
			";
		}

		else if($idregion!=0 && $idcommune==-1 && $idfokontany==-1){
			$sql = $sql."
			AND membre.id_region = $idregion
			";
		}

		//echo $sql;
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
			$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}	

	function getAllActiviteJPE() {
		$data = array();
		$sql = "SELECT * FROM activite
		INNER JOIN etablissement ON activite.id_etablissement = etablissement.id_etablissement
		INNER JOIN membre ON activite.id_membre = membre.id_membre
		AND activite.type_activite = 2
		";
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}

	function getAllActiviteStruct($type_activite, $type_etab) {
		$data = array();
		$sql = "";
		if ($type_activite==1){
			$sql = "SELECT * FROM activite 
			INNER JOIN etablissement ON activite.id_objet = etablissement.id_etablissement
			WHERE activite.type_activite = $type_activite AND activite.type_etab = $type_etab";
		}
		else if ($type_activite==2){
			$sql = "SELECT * FROM activite 
			INNER JOIN membre ON activite.id_objet = membre.id_membre
			WHERE activite.type_activite = $type_activite";
		}
		else if ($type_activite==3){
			$sql = "SELECT * FROM activite 
			INNER JOIN association ON activite.id_objet = association.id_association
			WHERE activite.type_activite = $type_activite";
		}
		else if ($type_activite==4){
			$sql = "SELECT * FROM activite 
			INNER JOIN ccj ON activite.id_objet = ccj.id_ccj
			WHERE activite.type_activite = $type_activite";
		}
		//echo $sql;
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}
	
	
	function getListCCJ() {
		$data = array();
		$sql = "SELECT * FROM ccj
		INNER JOIN region ON ccj.id_region = region.id_region
		INNER JOIN commune ON ccj.id_commune = commune.id_commune";	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
			$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}
	
	function getListVolet($type_volet) {
		$data = array();
		$sql = "";
		if($type_volet == 0){
			$sql = "SELECT * FROM volet
			INNER JOIN etablissement ON volet.id_etablissement =  etablissement.id_etablissement";
		}
		else {
			$sql = "SELECT * FROM volet
			INNER JOIN etablissement ON volet.id_etablissement =  etablissement.id_etablissement
			WHERE volet.type_volet = '$type_volet' ";
		}
		
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}

	function getListVoletSous($type_volet, $sous_type_volet){
		$data = array();
		$sql = "";
		if($type_volet == 0){
			$sql = "SELECT * FROM volet
			INNER JOIN etablissement ON volet.id_etablissement =  etablissement.id_etablissement";
		}
		else {
			$sql = "SELECT * FROM volet
			INNER JOIN etablissement ON volet.id_etablissement =  etablissement.id_etablissement
			WHERE volet.type_volet = '$type_volet' AND volet.sous_type_volet = '$sous_type_volet' ";
		}
		
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}

	function getListAbandonS() {
		$data = array();
		$sql = "SELECT * FROM abandonScolaire
		INNER JOIN etablissement ON abandonScolaire.id_etablissement =  etablissement.id_etablissement";		
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
			$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}	

	function getListEmploi() {
		$data = array();
		$sql = "SELECT * FROM emploi
		INNER JOIN etablissement ON emploi.id_etablissement =  etablissement.id_etablissement";		
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
			$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}

	function getListPersonnel() {
		$data = array();
		$sql = "SELECT * FROM situationPersonnelle
		INNER JOIN etablissement ON situationPersonnelle.id_etablissement =  etablissement.id_etablissement";		
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
			$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}
	function getListMembreCCJ() {
		$data = array();
		$sql = "SELECT * FROM membreccj 
		INNER JOIN ccj ON membreccj.id_ccj = ccj.id_ccj";		
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
			$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}

	function getAllEtablRegion($type, $id_region) {
		$data = array();
		$sql = "";
		if($type == 0){
			$sql = "SELECT * FROM etablissement
			INNER JOIN region ON etablissement.id_region = region.id_region
			INNER JOIN commune ON etablissement.id_commune = commune.id_commune
			INNER JOIN fokontany ON etablissement.id_fokontany = fokontany.id_fokontany 
			WHERE etablissement.id_region = '$id_region'
		";
		}
		else {
			$sql = "SELECT * FROM etablissement
			INNER JOIN region ON etablissement.id_region = region.id_region
			INNER JOIN commune ON etablissement.id_commune = commune.id_commune
			INNER JOIN fokontany ON etablissement.id_fokontany = fokontany.id_fokontany WHERE etablissement.type_etab = '$type' 
			AND etablissement.id_region = '$id_region'";
		}
		
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}

	function getAllCCJRegion($id_region) {
		$data = array();
		$sql = "SELECT * FROM ccj 
		INNER JOIN region ON ccj.id_region = region.id_region
		WHERE ccj.id_region = '$id_region'";	
		$Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}
	

	function deleteStructure($numid) {
		$data = array();
		$sql = "DELETE FROM etablissement WHERE numid = '$numid'";		
		$Q = $this->db->query($sql);
		$Q->free_result();
	}  
}

?>